#Utility function, can concatenate data frames with different collections of
#variables, filling in NA for those which are not shared. very useful for combining 
#sets of pitches, since the variable set in the gameday files can be pretty unstable. 
#Adapted from Sundar Dorai-Raj.
#
#It is presently unknown why the line 
#  x[[i]] <- x[[i]][1:nrow(x[[i]]),]
#is necessary, but some queries fail without it, with:
#  Error in value[[jj]][ri] <- if (is.factor(xij)) as.vector(xij) else xij :                                                         
#    replacement has length zero  

rbind.all <- function(...) {
   x <- list(...)
   cn <- unique(unlist(lapply(x, colnames)))
   for(i in seq(along = x)) {
     if(any(m <- !cn %in% colnames(x[[i]]))) {
       na <- matrix(NA, nrow(x[[i]]), sum(m))
       dimnames(na) <- list(rownames(x[[i]]), cn[m])
       x[[i]] <- cbind(x[[i]], na)
     }
   }

   cnames <- colnames(x[[1]])
   for(i in seq(along = x)) {
      x[[i]] <- x[[i]][1:nrow(x[[i]]),]
      ord <- match(cnames,colnames(x[[i]]))
      x[[i]] <- x[[i]][,ord]
   }

   do.call(rbind, x)
} 

getname.mysql <- function(first,last,condition=NULL,
    user,pass,db,host,query) {

  require (DBI)
  require (RMySQL)
  drv <- dbDriver("MySQL")
  con <- dbConnect(drv, username=user, password=pass, dbname=db, host=host)
  res <- dbSendQuery (con, query)
  names <- fetch(res,n=-1)  
  
  return(names)
}

getname.retrosheet <- function(first,last) getname.mysql(first,last,db="retrosheet",
    user="retrosheet",pass="",host="localhost",
    query=paste("SELECT * FROM id WHERE first='",first,"' AND last='",last,"'",sep=""))

getname.pitchfx <- function(first,last) getname.mysql(first,last,db="pitchfx",
    user="pitchfx",pass="",host="localhost",
    query=paste("SELECT * FROM players WHERE first='",first,"' AND last='",last,"'",sep=""))


read.retrosheet <- function(variables,condition,name=NULL,user="retrosheet",pass="",
  db="retrosheet",host="localhost",query=NULL,verbose=FALSE) {

  if(!is.null(name)) condition <- paste("events.BAT_ID='",
				    getname.retrosheet(strsplit(name," "))$id[1]
				    ,"'",sep="")


  query <- paste("SELECT",variables,"FROM events LEFT JOIN games ON events.GAME_ID = games.GAME_ID WHERE",condition)

  require (DBI)
  require (RMySQL)
  drv <- dbDriver("MySQL")
  con <- dbConnect(drv, username=user, password=pass, dbname=db, host=host)
  res <- dbSendQuery (con, query)
  events <- fetch(res,n=-1)
  names(events) <- tolower(names(events))

  return(events)

}

#Query a local MySQL database for pitch data. Designed for use with a database that
#conforms to Mike Fast's specifications.
read.pitchfx.mysql <- function(name=NULL,matchon="pitcher",clean=FALSE,user="pitchfx",pass="",
	db="pitchfx",host="localhost",query="",verbose=F,variables="*",year=2009,correct=FALSE) {

  if(!is.null(name) & matchon=="pitcher") {
    first<-strsplit(name," ")[[1]][1]
    last<-strsplit(name," ")[[1]][2]
    query<-paste("SELECT ",variables," FROM pitches LEFT JOIN ( ((atbats LEFT JOIN games ON atbats.game_id = games.game_id) LEFT JOIN players ON atbats . pitcher = players . eliasid) LEFT JOIN players batters ON atbats.batter=batters.eliasid ) ON pitches . ab_id = atbats . ab_id 
    LEFT JOIN parks ON (games.home=parks.park_id)
    WHERE players . last = '",last,"' AND players . first = '",first,
    "' AND YEAR(games.date) = ",year    ,
    sep="")
  }
  if(!is.null(name) & matchon=="batter") {
    first<-strsplit(name," ")[[1]][1]
    last<-strsplit(name," ")[[1]][2]
    query<-paste("SELECT ",variables," FROM pitches LEFT JOIN ( ((atbats LEFT JOIN games ON atbats.game_id = games.game_id) LEFT JOIN players ON atbats . batter = players . eliasid) LEFT JOIN players pitchers ON atbats.pitcher=pitchers.eliasid ) ON pitches . ab_id = atbats . ab_id 
    LEFT JOIN parks ON (games.home=parks.park_id)    
    WHERE  players . last = '",last,"' AND players . first = '",first,
    "' AND YEAR(games.date) = ",year ,sep="")
  }
  if(matchon=="var") {
    query<-paste("SELECT ",variables," FROM pitches LEFT JOIN ( ((atbats LEFT JOIN games ON atbats.game_id = games.game_id) LEFT JOIN players batters ON atbats . batter = batters . eliasid) LEFT JOIN players pitchers ON atbats.pitcher=pitchers.eliasid ) ON pitches . ab_id = atbats . ab_id WHERE ",query,sep="")
  }

  if(verbose) {
	print("Reading from database...")
  }

  require (DBI)
  require (RMySQL)
  drv <- dbDriver("MySQL")
  con <- dbConnect(drv, username=user, password=pass, dbname=db, host=host)
  res <- dbSendQuery (con, query)
  pitches <- fetch(res,n=-1)
  
  if(nrow(pitches)==0) {
    warning("No pitches found!")
    return(NULL)
  }
  
  attr(pitches,'class') <- c("pitchfx","data.frame")
  
  if(matchon=="batter" | matchon=="pitcher" | matchon=="var") {
    pinfo <- which(names(pitches)=="eliasid")[2]
    names(pitches)[pinfo:(pinfo+4)] <- paste(names(pitches)[pinfo:(pinfo+4)],".1",sep="")
    if(correct) {
      pitches$ax <- pitches$ax - pitches$delta_ax
      pitches$az <- pitches$az - pitches$delta_az
      pitches$vy0 <- pitches$vy0 - pitches$delta_vy0
      pitches$start_speed <- pitches$start_speed - pitches$delta_start_speed
    }
    drop <- which(colnames(pitches) %in% c('delta_ax','delta_ay','delta_vy0','delta_start_speed'))
    pitches <- pitches[,-drop]
  }
  
  if(verbose) {
	print("Processing pitches...")
  }
  if(clean) {
	classify <- ifelse(matchon=="pitcher",TRUE,FALSE)
	pitches <- clean.des(pitches,classify)
  }
  
  mysqlCloseConnection(con)
  return(pitches)
  
}
                        
#Low-level function called by read.pitchfx()
#parses the player file--useful for relating MLB id numbers to player names
read.pitchfx.getplayers<-function(webxml) {

  con<-textConnection(webxml)
  x<-xmlRoot(xmlTreeParse(readLines(con)))
  close(con)

  for(t in 1:2) {
    team <- x[[t]]
    team.vars <- xmlAttrs(team)
    names(team.vars) <- c("side","teamid","teamname")
    for(p in 1:length(team)) {
      if(names(team[p])=="player") {
	row <- xmlAttrs(team[[p]])
	row <- c(row,team.vars)
	names <- names(row)
	row <- data.frame(t(row))
	names(row) <- names
	if(exists("result",where=environment(),inherits=FALSE)) {
	  result <- rbind.all(result,row)
	} else {
	  result <- row
	}
      }
    }
  }

  for(i in c('id','first','last','boxname')) result[[i]] <- as.character(result[[i]])

  attr(result,'class') <- c("pitchfx.players","data.frame")
  return(result)
}

#low level function, determines game status. Useful for finding postponed games.
read.pitchfx.gamestatus<-function(webxml) {

  con<-textConnection(webxml)
  x<-xmlTreeParse(readLines(con),isURL=TRUE,getDTD=T,addAttributeNamespaces=T)
  close(con)
  
  r <- xmlRoot(x)
  row<-xmlAttrs(r)
  return(row[['status']])
}

#Low-level function that gets the list of all players who actually appeared in 
#a game.
#Used to avoid errors when reading in many games worth of pitches
read.pitchfx.playersused<-function(webxml) {
  
  idlist<-NA
  for(suffix in c("batters/","pitchers/")) {
    con<-url(paste(webxml,suffix,sep=""))
    x<-readLines(con)
    close(con)
    for(i in 1:length(x)) {
      if(substr(x[i],1,4)=="<li>") {
        idlist<-c(idlist,substr(x[i],14,19))
      }  
    }
  }
  return(idlist[-1])
}

#Retrieves general game information, such as home and away team and current record.
read.pitchfx.gameinfo<-function(webxml) {
  con<-textConnection(webxml)
  x<-xmlTreeParse(readLines(con),isURL=TRUE,getDTD=T,addAttributeNamespaces=T)
  close(con)
    
  r <- xmlRoot(x)
  gameinfo<-xmlAttrs(r)
  year<-substr(gameinfo['game_id'],1,4)
  month<-substr(gameinfo['game_id'],6,7)
  day<-substr(gameinfo['game_id'],9,10)
  away<-gameinfo['away_team_code']
  home<-gameinfo['home_team_code']
  away.wins<-gameinfo['away_wins']
  away.losses<-gameinfo['away_loss']
  home.wins<-gameinfo['home_wins']
  home.losses<-gameinfo['home_loss']
  output<-c(year,month,day,away,home,away.wins,away.losses,home.wins,home.losses)
  names(output)<-c("year","month","day","away","home","away.wins","away.losses","home.wins","home.losses") 
  
  return(output)
}

#Read data from an XML file on MLB's web site and convert to a data frame.
#By default, parses pitch data (pitcher or batter files)
#Can also call specialized sub-functions in order to get general game 
#information.

read.pitchfx<-function(webxml,mode="pitches") {

  require(XML)

  closeAllConnections()
  
  if(mode=="getplayers") {
    output<-read.pitchfx.getplayers(webxml)
    return(output)
  }
  if(mode=="gamestatus") {
    output<-read.pitchfx.gamestatus(webxml)
    return(output)
  }
    if(mode=="playersused") {
    output<-read.pitchfx.playersused(webxml)
    return(output)
  } 

  con<-textConnection(webxml)
  x<-xmlRoot(xmlTreeParse(readLines(con),isURL=TRUE,getDTD=T,addAttributeNamespaces=T))
  close(con)

  if(length(x)==0) return(NA)

  for(ab in 1:length(x)) {
    ab.vars <- xmlAttrs(x[[ab]])
    if("des" %in% names(ab.vars)) names(ab.vars)[names(ab.vars)=="des"] <- "des_ab"
    atbat <- x[[ab]]
    if(length(atbat)) {
      for(pitch in 1:length(atbat)) {
	pitch.vars <- xmlAttrs(atbat[[pitch]])
	row <- c(ab.vars,pitch.vars)
	if(exists("result",where=environment(),inherits=FALSE)) {
	  namelist <- names(row)
	  row <- data.frame(t(row))
	  names(row) <- namelist
	  result <- rbind.all(result,row)
	} else {
	  result <- data.frame(t(row))
	  names(result) <- names(row)
	}
      }
    }
  }

  for(i in c('inning','num','batter','b','s','o','b_height','x','y','start_speed','end_speed','sz_top','sz_bot','pfx_x','pfx_z','px','pz','x0','y0','z0','vx0','vy0','vz0','ax','ay','az','break_y','break_angle','break_length','type_confidence','spin_dir','spin_rate')) {
    result[[i]]<-as.numeric(as.character(result[[i]]))
  }

  for(i in c('des','des.1')) try(result[[i]] <- as.character(result[[i]]))

  class(result)<-c(class(result),"pitchfx")

  gameurl<-paste(substr(webxml,1,regexpr("/pbp",webxml)[[1]]),'boxscore.xml',sep="")   
  gameinfo<-read.pitchfx.gameinfo(gameurl)
  result$year<-gameinfo['year']
  result$month<-gameinfo['month']
  result$day<-gameinfo['day']
  result$away<-gameinfo['away']
  result$home<-gameinfo['home']
  result$away.wins<-gameinfo['away.wins']
  result$away.losses<-gameinfo['away.losses']
  result$home.wins<-gameinfo['home.wins']
  result$home.losses<-gameinfo['home.losses']
  result$atbatid<-paste(result$day,result$month,result$year,".",result$inning,result$num,sep="")
  
  closeAllConnections()
 
  return(result)

}

#Function that takes a player file and an MLB id number, and returns the player's name
#can also be used to convert from name to id number if "last" is specified
#Requires a file of player information, in the form returned by read.pitchfx.getplayers()
getname<-function(playerfile,batterid="",
		  first=playerfile$first[which(playerfile$id==batterid)],
		  last=playerfile$last[which(playerfile$id==batterid)]) {
  if(batterid!="") {
    output<-paste(first,last)
  } else {
    output<-playerfile$id[which(playerfile$last==last & playerfile$first==first)]
  }
  return(output)
}

#retrive the list of game identifiers for a given day
#Used by makeurl() when trying to find the relevant folder for a given team.
getgames<-function(year,month,day) {
  dayurl<-paste('http://gd2.mlb.com/components/game/mlb/year_',year,'/month_',month,'/day_',day,'/',sep="")
  con<-url(dayurl)
  x<-readLines(con)
  close(con)
  gamelist<-NA
  for(i in 1:length(x)) {
    if(length(grep("gid",x[i]))>0)  {
      gamelist<-c(gamelist,substr(x[i],14,43))
    } 
  }
  return(gamelist[-1])
}


#Generate a url for pitchf/x data on the MLB website. Used by read.pitchfx().
#Can also be used directly to generate URLs for other purposes.
#
#Many specifications provided, but the simplest is to specify only the dates and
#the team abbreviation (in the form stored in the variable "mlbteams"). In that 
#case, the function will use getgames() to figure out what folder to look in.
#Returns NA if the team did not play a game that day.

makeurl <- function(year,month,day,team1="",team2="",typefile="",id="",mode="pitches",game=0,team="") {
  if(game>0) {
    game<-getgames(year,month,day)[game]
    string1<-paste('http://gd2.mlb.com/components/game/mlb/year_',year,'/month_',month,'/day_',day,'/',game,'/',sep="")
  } else {
    string1<-paste('http://gd2.mlb.com/components/game/mlb/year_',year,'/month_',month,'/day_',day,'/gid_',year,'_',month,'_',day,'_',team1,'mlb_',team2,'mlb_1/',sep="")
  }
  if(team!="") {
    gamelist<-getgames(year,month,day)
    for(i in 1:length(gamelist)) {
      if(length(grep(team,gamelist[i]))>0) {
		game<-gamelist[i]
	  }
    }
    if(game==0) return(NA)
    string1<-paste('http://gd2.mlb.com/components/game/mlb/year_',year,'/month_',month,'/day_',day,'/',game,'/',sep="")
  }
  output<-paste(string1,'pbp/',typefile,'/',id,'.xml',sep="")
  if(mode=="players") {
  output<-paste(string1,'players.xml',sep="")
  } 
  if(mode=="game") {
  output<-paste(string1,'linescore.xml',sep="")
  }   
  if(mode=="playersused") {
  output<-paste(string1,'pbp/',sep="")
  } 
  return(output)
}

#Retrieve all pitches seen by specified player over specified time interval.
#Specify start date, end date, player's full name, and mlb abbreviated team 
#name (see above).
#
#If "ALL" is passed in the player name field, all at-bats for the specified
#team and time frame will be returned. (Warning: this can take a long time.)
#
#Also, be sure to specify the "pos" parameter for pitchers! otherwise you 
#will get an error, or that pitcher's hitting data if they are in the NL.

get.pitches<-function(player,pos="batters",team,startmonth,startday,endmonth,endday,year=2009,clean=TRUE,spin=FALSE,logging=TRUE,mysql=F,...) {

  if(mysql) {
    pos <- strsplit(pos,"s")[[1]][1]
    pitches <- read.pitchfx.mysql(name=player,matchon=pos,clean=clean,verbose=logging,year=year,...)
    return(pitches)
  }  

  if(logging) print("Checking the arguments...")

  get.team<-ifelse(player=="ALL",TRUE,FALSE)
  
  days.in.month<-c(31,28,31,30,31,30,31,31,30,31,30,31)

  fname<-strsplit(player," ")[[1]][1]
  lname<-strsplit(player," ")[[1]][2]

  initialized<-ifelse(get.team,TRUE,FALSE)
  iter<-1
  while(!initialized) {
  initmonth<-as.character(startmonth)
  initday<-as.character(startday)
  if(nchar(initmonth)==1) {
	initmonth<-paste("0",initmonth,sep="")
   }
  if(nchar(initday)==1) {
	initday<-paste("0",initday,sep="")
  }
  
  if(logging) print("Checking for data...")
  path<-makeurl(year,initmonth,initday,team=team,typefile=pos,id="123456")
   if(!is.na(path)) {
   gamestatus<-read.pitchfx(makeurl(year,initmonth,initday,team=team,mode="game"),mode="gamestatus")
    if(gamestatus=="Final" | gamestatus=="In Progress") { 
      players<-read.pitchfx(makeurl(year,initmonth,initday,team=team,mode="players"),mode="getplayers")
      playerlist<-read.pitchfx(makeurl(year,initmonth,initday,team=team,mode="playersused"),mode="playersused")
       #Make sure the player was used in the game       
        for(i in playerlist) {  
          if(lname %in% strsplit(getname(players,i)," ")[[1]] & fname %in% strsplit(getname(players,i)," ")[[1]]) {
            initialized<-TRUE
            mlbid<-getname(players,first=fname,last=lname)
			}
        } 
    }
   }
   if(iter>1 & !initialized) {
   startday<-startday+1
   if(startday>days.in.month[startmonth]) {
	startday<-1
	startmonth<-startmonth+1
   if(startday>endday & startmonth>=endmonth) {
	warning("No pitches found")
	return(invisible(1))
		}
	}
   }
   iter<-iter+1
  }
  

  initmonth<-as.character(startmonth)
  initday<-as.character(startday)
  if(nchar(initmonth)==1) {
	initmonth<-paste("0",initmonth,sep="")
  }
  if(nchar(initday)==1) {
	initday<-paste("0",initday,sep="")
  }

  players<-read.pitchfx(makeurl(year,initmonth,initday,team=team,mode="players"),mode="getplayers")
  
  if(logging) print("Starting the download...")
  if(get.team) {
    test.str<-paste("_",team,sep="")
    game.str<-makeurl(year,initmonth,initday,team=team,typefile=pos)
    side<-ifelse(gregexpr(test.str,game.str)[[1]][1]==87,"home","away")
    players<-players[which(players$side==side),]
    if(pos=="batters") {
		players<-players[-which(players$position=="P"),]
	}
    if(pos=="pitchers") {
		players<-players[-which(players$position!="P"),]
	}
    playerlist<-read.pitchfx(makeurl(year,initmonth,initday,team=team,mode="playersused"),mode="playersused")
    first<-TRUE  
    for(p in players$id) {
      if(p %in% playerlist) {
      player<-read.pitchfx(makeurl(year,initmonth,initday,team=team,typefile=pos,id=p))
      if(length(player)>1) player$name <- getname(players,p)
      if(first & length(player)>1) {
		hitter<-player
        first<-FALSE
      } else if(length(player)>1) {
              hitter<-rbind.all(hitter,player)
              }
      }
    }
  } else {
    mlbid<-getname(players,first=fname,last=lname)
    hitter<-read.pitchfx(makeurl(year,initmonth,initday,team=team,typefile=pos,id=mlbid))
    hitter$name <- getname(players,mlbid)
  }

  if(logging) print("Scanning pitches...")  
  for(j in startmonth:endmonth) {
  month<-as.character(j)
  if(nchar(month)==1) {
	month<-paste("0",month,sep="")
  }
  day1<-1
  day2<-days.in.month[j]
  if(j==startmonth) {
	day1<-startday
	}
  if(j==endmonth) {
	day2<-endday
  }
  

  for(i in day1:day2) {
  if(logging) print(c("day",j,i))
  skip<-FALSE
  if(i==startday & j==startmonth) {
	skip=TRUE
	}
    day<-as.character(i)
    if(nchar(day)==1) {
		day<-paste("0",day,sep="")
	}
    #Make sure a game was scheduled that day
    path<-makeurl(year,month,day,team=team,typefile=pos)
    if(logging) print(path)
    if(!is.na(path) & !skip) {
      #Make sure the game was actually played
      gamestatus<-read.pitchfx(makeurl(year,month,day,team=team,mode="game"),mode="gamestatus")
      if(gamestatus=="Final" | gamestatus=="In Progress") {
        playerlist<-read.pitchfx(makeurl(year,month,day,team=team,mode="playersused"),mode="playersused")
        if(get.team) {
          players<-read.pitchfx(makeurl(year,month,day,team=team,mode="players"),mode="getplayers")
          game.str<-makeurl(year,month,day,team=team,typefile=pos)
          side<-ifelse(gregexpr(test.str,game.str)[[1]][1]==87,"home","away")
          players<-players[which(players$side==side),]
          if(pos=="batters") {
			players<-players[-which(players$pos=="P"),]
			}
          if(pos=="pitchers") {
			players<-players[-which(players$pos!="P"),]
			}
          for(p in players$id) {
            if(p %in% playerlist) { 
            path<-makeurl(year,month,day,team=team,typefile=pos,id=p)
            newdata<-read.pitchfx(path)
            if(length(newdata)>1) {
	      newdata$name <- getname(players,p)
              hitter<-rbind.all(hitter,newdata)
            }
          } 
          }       
        } else {
        path<-makeurl(year,month,day,team=team,typefile=pos,id=mlbid)
        #Make sure the player was used in the game
        ingame<-(mlbid %in% playerlist)
        if(ingame) {
          newdata<-read.pitchfx(path)
          if(length(newdata)>1) {
	    newdata$name <- getname(players,mlbid)
            hitter<-rbind.all(hitter,newdata)
          }
        }
        }
        }
    }
  }
  }

  if(logging) print("cleanup")
  if(clean) {
	classify <- ifelse(pos=="pitchers",TRUE,FALSE)
	hitter<-clean.des(hitter,classify)
	}
  if(spin) {
	hitter<-cbind(hitter,spin(hitter))
  }
  attr(hitter,'class') <- c("pitchfx","data.frame")
  return(hitter)
}

#A function that takes a pitchfx object and cleans it up for ease of analysis.
#The routine adds some new variables:
#
#  outcome      :   a cleaned up version of des_ab, showing the outcome of the 
#                   pitch.
#  calledstrike :   whether the pitch was called a ball or a strike. Has valid
#                   values only for pitches not swung at. Required for calculating
#		    a selectivity curve.
#
#The function also cleans up the "b" and "s" variables to accurately reflect the
# count. (In the original data, these variables contain the balls and strikes for
# the final pitch of the at-bat, not for the current pitch.) The "count" flavor
# of plot will not work correctly without this modification.
#
#This function can be automatically called by get.pitches() using an option.

clean.des <- function(pitches,classify = FALSE) {
  pitches$outcome<-NA
  levels(pitches$outcome)<-c("Called strike","Swinging strike","Foul","Ball","Reached base","In play, out","HBP") 
  if("des_ab" %in% names(pitches)) {
    temp<-as.character(pitches$des)
    ev<-pitches$brief_event
    from.mysql<-FALSE
  } else {
    temp<-as.character(pitches$des)
    ev<-pitches$event
    from.mysql<-TRUE
  } 

  pitches$outcome[temp %in% c("Automatic Ball","Ball","Ball In Dirt","Intent Ball","Pitchout")] <- "Ball"
  pitches$outcome[temp %in% c("Called Strike")] <- "Called strike"
  pitches$outcome[temp %in% c("Foul","Foul (Runner Going)","Foul Bunt")] <- "Foul"
  pitches$outcome[temp %in% c("Hit by Pitch")] <- "HBP"
  pitches$outcome[temp %in% c("In play, out(s)")] <- "In play, out"
  pitches$outcome[temp %in% c("In play, no out","In play, run(s)")] <- "Reached base"
  pitches$outcome[temp %in% c("Unknown Strike")] <- "Strike"
  pitches$outcome[temp %in% c("Foul Tip","Missed Bunt","Swinging Strike","Swinging Strike (Blocked)")] <- "Swinging strike"
    
  if(length(which(pitches$outcome=="Reached base"))>0) {
    pitches$outcome[which(pitches$outcome=="Reached base" & ev %in% c("Groundout","Line out","Fly out","Rnr out","Strikeout","Pop out","Bunt out"))]<-"In play, out"   
  }
 pitches$calledstrike<-NA
 pitches$calledstrike[pitches$outcome=="Called strike"]<-"Took strike"
 pitches$calledstrike[pitches$outcome=="Ball"]<-"Took ball"
 pitches$calledstrike<-factor(pitches$calledstrike)
 
 if(!from.mysql) {
  atbats<-unique(pitches$atbatid)
  if(length(atbats)) for(i in 1:length(atbats)) {
    select<-which(pitches$atbatid==atbats[i])
    pitches[select[1],]$b<-0
    pitches[select[1],]$s<-0
    if(length(select)>1) for(j in 2:length(select)) {
      pitches[select[j],]$b<-pitches[select[j-1],]$b
      pitches[select[j],]$s<-pitches[select[j-1],]$s
      if(!is.na(pitches[select[j-1],]$type) ){
	if(pitches[select[j-1],]$type=="B") pitches[select[j],]$b<-pitches[select[j],]$b+1
	if(pitches[select[j-1],]$type=="S" & pitches[select[j],]$s<2) pitches[select[j],]$s<-pitches[select[j],]$s+1
      }
    }
   }
 }
  
 if(from.mysql) {
  pitches$hit_event<-ifelse(pitches$event %in% c("Double","Field Error","Fielders Choice","Fielders Choice Out","Fly Out","Force Out","Ground Out","Grounded Into DP","Home Run","Line Out","Pop Out","Sac Fly","Sac Fly DP","Single","Bunt Ground Out","Bunt Pop Out","Triple"),pitches$event,NA)
  pitches$hit_event[which(pitches$type!="X")]<-NA
  pitches$goodhit<-ifelse(pitches$hit_event %in% c("Single","Double","Triple","Home Run","Line Out"),1,ifelse(pitches$hit_event %in% c("Field Error","Fly Out","Force Out","Ground Out","Grounded Into DP","Pop Out","Fielders Choice","Fielders Choice Out"),0,NA))
  pitches$goodhit[which(pitches$type!="X")]<-NA

  pitches$b<-pitches$ball
  pitches$s<-pitches$strike
    
  game.pcounts<-table(pitches$game_id)[order(rownames(table(pitches$game_id)))]
  pcounts<-unlist(sapply(game.pcounts,function(x) 1:x))
  pitches$pitchcount<-pcounts
  
 }
   
 if(classify) pitches <- classify.pitches(pitches)

  return(pitches)
}
